MySQL 8.0.19亿级数据如何秒速增加字段？ |
您所在的位置：网站首页 › mysql 单表过亿 › MySQL 8.0.19亿级数据如何秒速增加字段？ |
MySQL 8.0.19亿级数据如何秒速增加字段？
|
概述
今天主要介绍一下MySQL 8.0.19 instant add column的新特性，基于亿级数据秒速增加字段，下面一起来看看吧~ 一、MySQL DDL 的方法MySQL 在大型表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视，而MySQL 的 DDL 有很多种方法。 MySQL 本身自带三种方法，分别是：copy、inplace、instant。 copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 算法时，会自动判断，能使用 not-rebuild-table 的情况下会尽量使用，不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。从 MySQL 8.0.12 开始，引入了 instant 算法并且默认使用。目前 instant 算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。有一些第三方工具也可以实现 DDL 操作，最常见的是 percona 的 pt-online-schema-change 工具（简称为 pt-osc），和 github 的 gh-ost 工具，均支持 MySQL 5.5 以上的版本。 》》各类工具的对比 一般情况下的建议： 如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据（例如增加列），不关心主从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用 gh-ost二、MySQL DDL 的原理简析1、copy 算法较简单的实现方法，MySQL 会建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。MySQL 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（> /etc/profile source /etc/profile 2.1.2、数据准备 --准备2张表，每张表1亿数据 >create database sbtest; nohup sysbench --mysql-host=localhost --mysql-port=53306 --mysql-user=root --mysql-password=axxxpx \ --test=/usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 \ --oltp-table-size=100000000 --num-threads=50 --rand-init=on prepare & 2.2、添加字段 mysql 5.7 用inplace算法去对一亿的表添加字段，耗时接近10分钟，MySQL8.0 用instant算法对一亿的表添加字段，耗时0.12s --指定InPlace算法添加列，(5.7版本添加列使用该算法) alter table sbtest1 add column cityname1 varchar(10) , algorithm=inplace; --指定 instant 算法添加列（8.0版本添加列使用该算法） alter table sbtest2 add column cityname2 varchar(10) , algorithm=instant; 2.3、设置默认值和删除默认值 alter table sbtest1 alter column cityname1 set default 'wuhan' ,algorithm=inplace,lock=default; alter table sbtest2 alter column cityname2 set default 'beijing',algorithm=instant,lock=default; alter table sbtest1 alter column cityname1 drop default ,algorithm=inplace; alter table sbtest2 alter column cityname2 drop default,algorithm=instant; 2.4、修改列操作 --inplace算法和instant算法均不支持 alter table sbtest1 modify cityname1 datetime; 2.5、虚拟列的增加和删除 alter table sbtest1 add column (d int generated always as (k+1) virtual),algorithm=inplace; alter table sbtest2 add column (d int generated always as (k+1) virtual),algorithm=instant; alter table sbtest1 drop column d,algorithm=inplace; alter table sbtest2 drop column d,algorithm=instant; 2.6、增加带有外键的列 --设置ON UPDATE CURRENT_TIMESTAMP，表示在数据库数据有更新的时候createtime的时间会自动更新 alter table sbtest1 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=inplace; alter table sbtest2 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=instant; 2.7、修改表名 alter table sbtest1 rename to sbtest11, algorithm=inplace; alter table sbtest2 rename to sbtest22, algorithm=instant; 基于上面的测试可以发现，在快速加列功能上使用 instant 算法添加列基本都在 1s 内完成，而使用 5.7 版本的 inplace 算法时，则添加列的时间上升至数分钟。对比看来 8.0 版本的这个特性确实很实用！ 来源： https://www.toutiao.com/i6933566079608439308/ |
【本文地址】
今日新闻 |
推荐新闻 |